# -*- coding: utf-8 -*-
"""
@name:XXX
@Date: 2019/11/4 17:31
@Version: v.0.0
"""
from typing import Any, Union
import pymysql
import configparser
from pymysql.connections import Connection
from pymysql.cursors import Cursor

#目标库
class SaveMySQL:
    db: Connection
    cursor: Union[Cursor, Any]

    def __init__(self):
        self.config = configparser.ConfigParser()
        self.con_db()

    def open_data(self):
        with open('data.txt', 'r', encoding='utf-8') as f:
            data = f.read()
        print(data)

    def con_db(self):
        self.config.read('config.ini', encoding='utf-8-sig')
        self.db = pymysql.connect(
            host=self.config['SaveMysql']['host'],
            port=int(self.config['SaveMysql']['port']),
            user=self.config['SaveMysql']['user'],
            passwd=self.config['SaveMysql']['password'],
            db=self.config['SaveMysql']['db'],
            charset=self.config['SaveMysql']['charset']
        )
        self.cursor = self.db.cursor()

    '''更新公司基本信息'''
    def update_message(self, table, data_list):
        """更新基本信息"""
        keys_list = ['SYSID', '电话', '邮箱', '网址', '地址', '简介', '工商注册号', '组织机构代码', '统一信用代码', '公司类型',
                     '纳税人识别号', '行业', '营业期限', '登记机关', '注册地址', '英文名称', '经营范围', '公司名称', '法人',
                     '注册资本', '注册时间', '核准日期', '企业状态', '纳税人资质', '人员规模', '实缴资本', '参保人数']
        keys = ','.join(["%s"] * len(keys_list)) % tuple(keys_list)
        values = ', '.join(["'%s'"] * (len(data_list)))
        sql = "INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE ".format(
            table=table, keys=keys, values=values)
        update = ','.join(["{key}='%s'".format(key=key) for key in tuple(keys_list)])
        sql += update
        sql = sql % tuple(data_list * 2)
        try:
            if self.cursor.execute(sql):
                self.db.commit()
        except Exception:
            print(Exception.args)
            self.db.rollback()

    def delete_other(self, table, gszch):
        """
        删除其他信息
        :return:
        """
        # number = data_list[0][0]
        sql = "DELETE FROM {table} WHERE 工商注册号='{num}';".format(table=table, num=gszch)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception:
            self.db.rollback()




    def insert_other(self, table, data_list):
        """其他信息保存"""
        value = ""
        data_lists = []
        for data in data_list:
            value = ', '.join(["%s"] * (len(data)))
            data_lists.append(tuple(data))
        sql = 'INSERT INTO {table} VALUE (NULL, {value});'.format(table=table, value=value)
        #print(sql)
        try:
            if self.cursor.executemany(sql, data_lists):
                self.db.commit()
                return '',''
        except Exception as e:
            self.db.rollback()
            return f'{e.args} 存入数据库有误',table


    def __del__(self):
        self.db.close()

#源数据库
class ReadMySQL:
    db: Connection
    cursor: Union[Cursor, Any]

    def __init__(self):
        self.config = configparser.ConfigParser()
        self.config.read('config.ini', encoding='utf-8-sig')
        self.table = self.config['ReadMysql']['table']
        self.con_db()

    def con_db(self):
        self.db = pymysql.connect(
            host=self.config['ReadMysql']['host'],
            port=int(self.config['ReadMysql']['port']),
            user=self.config['ReadMysql']['user'],
            passwd=self.config['ReadMysql']['password'],
            db=self.config['ReadMysql']['db'],
            charset=self.config['ReadMysql']['charset'])
        self.cursor = self.db.cursor()


    def read_data(self):
        sql = "SELECT 统一信用代码,公司名称 FROM {table} WHERE 上传状态=0;".format(table=self.table)
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception:
            pass

    def read_already_update(self):
        sql="SELECT COUNT(上传状态) FROM {table} WHERE 上传状态=1;".format(table=self.table)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return self.cursor.fetchall()
        except Exception:
            pass

    '''生成失信人excel'''
    def query_data(self):
        sql = "select a.`公司名称` as name,(select group_concat(concat(`姓名`,'(',`职务`,')') Separator '、') from `主要人员` where 工商注册号 =a.工商注册号) as iname,(case WHEN c.`案号` IS NULL then '0'  else '1' end) as shixin,c.`案号` as caseCode,left(a.注册地址,3) as province,c.`发布日期` as publishdate,c.`立案日期` as regdate,a.`法人` as businessentity,c.`失信被执行人行为具体情形` as disrupttypename,c.`身份号码或组织机构代码` as cardnum,c.`执行法院` as courtname, c.`省份` as courtarea,'' as sexy,c.`执行法院` as gistunit,c.`履行状态` as performance,c.`详情` as duty,c.`执行依据文号` as gistId from 基本信息 as a  left join  失信人 as c on a.工商注册号=c.工商注册号"
        #print(sql)
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception:
            pass

    '''生成高管excel'''
    # def query_gg_data(self):
    #     sql = "select a.`公司名称`,b.`姓名`,b.`职务` FROM `基本信息` as a left join `主要人员` as b on a.`工商注册号` = b.`工商注册号`"
    #     #print(sql)
    #     try:
    #         self.cursor.execute(sql)
    #         return self.cursor.fetchall()
    #     except Exception:
    #         pass


    '''更新原公司信息表，上传状态'''
    def upload_data(self, data):
        sql = "UPDATE {table} SET 上传状态=1 WHERE 公司名称='{data}';".format(table=self.table, data=data)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return self.cursor.rowcount
        except Exception:
            self.db.rollback()

    #清空所有表
    def clera_all_table(self):
        tables = ['基本信息','失信人','主要人员','原公司信息']
        for i in tables:
            sql = 'truncate table %s' % i
            print(sql)
            try:
                self.cursor.execute(sql)
                self.db.commit()
            except Exception as e:
                return e
                continue
        # sql2 = 'select count(id) from comm_info'
        # self.cursor.execute(sql2)
        number = self.cursor.rowcount
        # if number:
        #         #     number =number[0][0]
        return number



if __name__ == '__main__':
    mysql = SaveMySQL()
    mysql.insert_other('股东变更',[['510109000025957', '1', '周波', '5.0%', '0.0%', '2014-03-20', '2015-05-14']])
    # mysql.insert_other('test', data_l)
    # mysql.open_data()
    # mysql.update_message('message', data_li)
    # r=ReadMySQL()
    # r.upload_data('110000450007248')
    # a=r.read_already_update()
    # print(a)